package net.cmkj.mine.core.admin.module;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.jfinal.aop.Before;
import com.jfinal.kit.PropKit;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.ehcache.CacheKit;
import net.cmkj.mine.bean.*;
import net.cmkj.mine.common.cache.CacheClearUtils;
import net.cmkj.mine.common.cache.CacheName;
import net.cmkj.mine.common.model.DataGridModel;
import net.cmkj.mine.common.model.InvokeResult;
import net.cmkj.mine.interceptor.ModulesInterceptor;
import net.cmkj.mine.util.CommonUtils;
import net.cmkj.mine.util.DataGridModelUtils;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ModulesService {
	
	private static Logger log = LogManager.getLogger(ModulesService.class);
	private Modules dao = Modules.dao;
	private Columns cdao = Columns.dao;
	
	private String table = PropKit.use("table.txt").get("table");
	
	/**
	 * 获取模块列表
	 * @param pageNumber
	 * @param pageSize
	 * @param name
	 * @param status
	 * @param orderbyStr
	 * @return
	 * @throws Exception
	 */
	@Before(ModulesInterceptor.class)
	public DataGridModel getList(int pageNumber, int pageSize, String name, Integer status, String orderbyStr) throws Exception {
		log.info("获取模块列表");
		Page<Modules> pageInfo = dao.paginate(pageNumber, pageSize, table, name, status, orderbyStr);
		DataGridModel result = DataGridModelUtils.toDataGridView(pageInfo);
		return result;
	}

	/**
	 * 获取表字段信息
	 * @param pageNumber
	 * @param pageSize
	 * @param moduleid
	 * @param name
	 * @param status
	 * @param orderbyStr
	 * @return
	 * @throws Exception
	 */
	public DataGridModel getColumnsList(int pageNumber, int pageSize, long moduleid, String name, Integer status, String orderbyStr) throws Exception {
		log.info("获取表字段信息");
		/*List<Record> list = cdao.getColumnsList(tablename);
		DataGridModel result = new DataGridModel();
		result.setData(list);
		result.setCount(list.size());*/
		Page<Columns> pageInfo = cdao.getList(pageNumber, pageSize, moduleid, name, status, orderbyStr);
		DataGridModel result = DataGridModelUtils.toDataGridView(pageInfo);
		return result;
	}

	/**
	 *获取表字段列表
	 * @param moduleid
	 * @return
	 * @throws Exception
	 */
	public List<Columns> getColumnsList(long moduleid) throws Exception {
		log.info("获取表字段列表");
		return cdao.getList(moduleid);
	}

	/**
	 * 获取已设置查询字段列表
	 * @param moduleid
	 * @return
	 * @throws Exception
	 */
	/*public Object[] getSearchedList(long moduleid) throws Exception {
		log.info("获取已设置查询字段列表");
		return CommonUtils.listToArr(cdao.getSearchedList(moduleid));
	}*/

	/**
	 * 获取已设置查询字段列表
	 * @param moduleid
	 * @return
	 * @throws Exception
	 */
	public List<String> getSearchedList(long moduleid) throws Exception {
		log.info("获取已设置查询字段列表");
		List<String> result = new ArrayList<String>();
		Modules module = findById(moduleid);
		String searchids = module.getStr("searchCids");
		if (StrKit.notBlank(searchids)) {
			String[] ids = searchids.split(",");
			for (String id : ids) {
				result.add(id);
			}
		}
		return result;
	}

	/**
	 * 设置自定义查询项
	 * @param moduleid
	 * @param data
	 * @return
	 * @throws Exception
	 */
	public InvokeResult saveColumnsSearch(long moduleid, String data) throws Exception {
		/*boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Db.update("delete from column_search where moduleid = ?", moduleid);
				List<JSONObject> searches = JSONArray.parseArray(data, JSONObject.class);
				for (JSONObject search : searches) {
					Db.update("insert into column_search(moduleid, columnid) values (?, ?)", new Object[]{moduleid, search.getLong("value")});
				}
				return true;
			}
		});*/
		StringBuffer searchCids = new StringBuffer();
		List<JSONObject> searches = JSONArray.parseArray(data, JSONObject.class);
		for (int i = 0; i < searches.size(); i++) {
			JSONObject search = searches.get(i);
			if (i == searches.size() - 1) {
				searchCids.append(search.getLong("value"));
			} else {
				searchCids.append(search.getLong("value")+",");
			}
		}
		Modules module = findById(moduleid);
		module.setSearchCids(searchCids.toString());
		boolean flag = module.update();
		if (flag) {
			return InvokeResult.success();
		} else {
			return InvokeResult.failure("设置自定义查询项失败！");
		}
	}

	/**
	 * 获取表字段数量
	 * @param moduleid
	 * @return
	 * @throws Exception
	 */
	public int getColumnCount(long moduleid) throws Exception {
		log.info("获取表字段数量");
		return cdao.getCount(moduleid);
	}
	
	/**
	 * 获取模块信息
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public Modules findById(long id) throws Exception {
		log.info("获取模块信息");
		return dao.findById(id);
	}

	/**
	 * 根据访问路径获取模块
	 * @param url
	 * @return
	 * @throws Exception
	 */
	public Modules getModuleByUrl(String url) throws Exception {
		return dao.getModuleByUrl(url, table);
	}

	/**
	 * 获取字段信息
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public Columns findColumnsById(long id) throws Exception {
		log.info("获取字段信息");
		return cdao.findById(id);
	}
	
	/**
	 * 获取表字段
	 * @param tablename
	 * @param column
	 * @param param
	 * @return
	 * @throws Exception
	 */
	public Record getColumn(String tablename, String column, Object param) throws Exception {
		Record result = cdao.getColumn(tablename, column, param);
		String column_type = result.getStr("column_type");
		if (column_type.indexOf("(") > -1) {
			column_type = column_type.substring(column_type.indexOf("(")+1,column_type.indexOf(")"));
			String[] types = column_type.split(",");
			result.set("length", types[0]);
			if (types.length == 2) {
				result.set("decimal", types[1]);
			}
		}
		return result;
	}

	/**
	 * 模块信息保存
	 * @param module
	 * @param column_comments
	 * @param column_types
	 * @param lengths
	 * @param decimals
	 * @param column_defaults
	 * @param is_nullables
	 * @return
	 * @throws Exception
	 */
	public InvokeResult save(Modules module, SysUser user, String[] column_comments, String[] column_types, Long[] foreignTables, String[] foreignKeys, Integer[] lengths, Integer[] decimals, String[] column_defaults, String[] is_nullables, Integer pid) throws Exception {
		log.info("模块信息保存及表单创建");
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				try {
                    String ntn = getNextTableNum();
					SysRes res = new SysRes();
					res.setPid(pid);
					res.setIconcls(module.getIconcls());
					res.setName(module.getName());
					res.setUrl("/sys_admin/mineservice?moduleurl="+table+ntn);
					Record record = Db.findFirst("SELECT MAX(seq) seq FROM `sys_res` where pid = ?", pid);
					if(record.get("seq")==null){
						res.setSeq(1);
					}else{
						res.setSeq(Integer.valueOf(record.get("seq").toString())+1);
					}
					res.setType(1);
					res.setCreatedate(new Date());
					res.setStatus(1);
					res.setLevel(0);
					if (!res.save()) {
						return false;
					}
					for (int i = 0; i <7 ; i++) {
						SysRes btn = new SysRes();
						btn.setPid(res.getId());
						if(i==0){
							btn.setName("添加");
                            btn.setUrl(table+ntn+"btnadd");
						}else if(i==1){
                            btn.setName("导入");
                            btn.setUrl(table+ntn+"btnimport");
						}else if(i==2){
                            btn.setName("导出");
                            btn.setUrl(table+ntn+"btnexport");
						}else if(i==3){
                            btn.setName("详情");
                            btn.setUrl(table+ntn+"btndetail");
						}else if(i==4){
                            btn.setName("编辑");
                            btn.setUrl(table+ntn+"btnedit");
						}else if(i==5){
                            btn.setName("删除");
                            btn.setUrl(table+ntn+"btndel");
						}else if(i==6){
                            btn.setName("附件");
                            btn.setUrl(table+ntn+"btnattach");
						}
						btn.setSeq(i+1);
						btn.setType(2);
						btn.setCreatedate(new Date());
						btn.setStatus(1);
						btn.setLevel(0);
						if (!btn.save()) {
							return false;
						}
					}
					int collength = column_comments.length;
					module.setTablename(ntn);
					module.setLastCnum(getNum(collength));
					module.setUrl(table+ntn);
                    module.setSysres(res.getId());
					if (!dao.save(module, user)) {
						return false;
					}
					String tablename = table+ntn;
					String dropstr = "DROP TABLE IF EXISTS "+tablename;
					String createstr = "CREATE TABLE "+tablename+" (id BIGINT NOT NULL AUTO_INCREMENT,";
					for (int i = 0; i < collength; i++) {
						Columns column = new Columns();
						String type = column_types[i];
                        Integer length = lengths[i];
						if (!"text".equalsIgnoreCase(type) && !"longtext".equalsIgnoreCase(type) && !"date".equalsIgnoreCase(type) && !"datetime".equalsIgnoreCase(type)) {
							if ("select".equalsIgnoreCase(type)) {
								column.setForeignTable(foreignTables[i]);
								column.setForeignKey(foreignKeys[i]);
								type = "bigint";
							} else if ("sex".equalsIgnoreCase(type) || "yesorno".equalsIgnoreCase(type) || "haveorno".equalsIgnoreCase(type)) {
								type = "int";
							}else if ("file".equalsIgnoreCase(type) ) {
                                type = "varchar";
                            }
							if (length != null && length > 0) {
								type += "("+length;
								if (decimals[i] != null && decimals[i] > 0) {
									type += ", "+decimals[i];
								}
								type += ")";
							}else{
                                if(type.equalsIgnoreCase("varchar")){
                                    type += "(255)";
                                    length=255;
                                }else if(type.equalsIgnoreCase("int")){
                                    type += "(11)";
                                    length=11;
                                }else if(type.equalsIgnoreCase("bigint")){
                                    type += "(20)";
                                    length=20;
                                }else if(type.equalsIgnoreCase("double")){
                                    type += "(11,2)";
                                    length=11;
                                }
                            }
						}
						String notnull = "NULL";
						if ("NO".equalsIgnoreCase(is_nullables[i])) {
							notnull = "NOT NULL";
						}
						String defaultstr = "";
						if (CommonUtils.isNotEmpty(column_defaults[i])) {
							defaultstr = " DEFAULT "+column_defaults[i];
						}
						String nextnum = getNum(i+1);
						createstr += " field"+nextnum+" "+type+" "+notnull+" "+defaultstr+" COMMENT '"+column_comments[i]+"',";
						column.setColumnComment(column_comments[i]).setColumnName("field"+nextnum).setColumnType(column_types[i]).setLengths(length)
								.setDecimals(decimals[i]).setColumnDefault(column_defaults[i]).setIsNullable(is_nullables[i]).setModuleid(module.getId())
								.setOrdinalPosition(i+2);
						if (!column.save()) {
							return false;
						}
					}
					createstr += "createuser INT NULL COMMENT '创建人',deptid BIGINT NULL COMMENT '部门ID',createdate datetime NULL COMMENT '创建时间',  PRIMARY KEY (`id`));";
					if (!cdao.createTable(dropstr, createstr)) {
						return false;
					}
					if (module.getIfAttach() == 1) {
						if (!cdao.createFileTable(tablename)) {
							return false;
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
					log.error("模块信息保存及表单创建异常："+e.getMessage());
					return false;
				}
				return true;
			}
		});
		if (flag) {
			return InvokeResult.success();
		} else {
			return InvokeResult.failure("模块信息保存及表单创建失败！");
		}
	}

    public InvokeResult update(Modules module,Integer pid,SysUser user) throws Exception {
        log.info("修改模块信息");
        boolean flag = Db.tx(new IAtom() {
            @Override
            public boolean run() throws SQLException {
                if(module.getId()==null){
                    return false;
                }
                try {
                    Modules newModule = Modules.dao.findById(module.getId());
                    newModule.setName(module.getName());
                    newModule.setIconcls(module.getIconcls());
                    newModule.setIfAttach(module.getIfAttach());
                    if (!newModule.update()) {
                        return false;
                    }
                    if (newModule.getIfAttach() == 1) {
                        if (!cdao.createFileTable(table+newModule.getTablename())) {
                            return false;
                        }
                    }else{
                        if (!cdao.deleteFileTable(table+newModule.getTablename())) {
                            return false;
                        }
                    }
                    SysRes sysRes = SysRes.dao.findById(newModule.getSysres());
                    if (!sysRes.setPid(pid).setName(newModule.getName()).setIconcls(newModule.getIconcls()).update()) {
                        return false;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    log.error("修改模块信息异常："+e.getMessage());
                    return false;
                }
                return true;
            }
        });
        if (flag) {
            SysRes.dao.getSysResList(new ArrayList<SysRes>(), user.getId(),1);
            SysRes.dao.getSysUserMenuView( new StringBuffer(),user.getId());
            return InvokeResult.success();
        } else {
            return InvokeResult.failure("修改模块信息失败！");
        }
    }

	/**
	 * 模块信息删除
	 * @param ids
	 * @return
	 * @throws Exception
	 */
	public InvokeResult delete(String ids,SysUser user) throws Exception {
		log.info("模块信息删除");
		return dao.deleteByIds(ids,user);
	}
	
	/**
	 * 模块状态变更
	 * @param ids
	 * @param status
	 * @return
	 * @throws Exception
	 */
	public InvokeResult setStatus(String ids, Integer status,SysUser user) throws Exception {
		log.info("模块状态变更");
		return dao.setStatus(ids, status,user);
	}

	/**
	 * 模块状态变更
	 * @param ids
	 * @param isshow
	 * @return
	 * @throws Exception
	 */
	public InvokeResult setColumnShow(String ids, Integer isshow) throws Exception {
		log.info("模块状态变更");
		return cdao.setColumnShow(ids, isshow);
	}

	/**
	 * 创建模块表
	 * @param column_comments
	 * @param column_types
	 * @param lengths
	 * @param decimals
	 * @param column_defaults
	 * @param is_nullables
	 * @return
	 * @throws Exception
	 */
	public InvokeResult createTable(String[] column_comments, String[] column_types, Integer[] lengths, Integer[] decimals, String[] column_defaults, String[] is_nullables) throws Exception {
		String tablename = table+getNextTableNum();
		String dropstr = "DROP TABLE IF EXISTS "+tablename;
		String createstr = "CREATE TABLE "+tablename+" (id BIGINT NOT NULL AUTO_INCREMENT, ";
		for (int i = 0; i < column_comments.length; i++) {
			String type = column_types[i]+"("+lengths[i];
			if (decimals[i] != null) {
				type += ", "+decimals[i];
			}
			type += ")";
			String notnull = "NULL";
			if ("NO".equalsIgnoreCase(is_nullables[i])) {
				notnull = "NOT NULL";
			}
			String defaultstr = "";
			if (CommonUtils.isNotEmpty(column_defaults[i])) {
				defaultstr = " DEFAULT "+column_defaults[i];
			}
			createstr += " field"+getNum(i+1)+" "+type+" "+notnull+" "+defaultstr+" COMMENT "+column_comments[i]+",";
		}
		createstr += " PRIMARY KEY (`id`));";
		if (cdao.createTable(dropstr, createstr)) {
			return InvokeResult.success();
		} else {
			return InvokeResult.failure("模块创建失败！");
		}
	}

	/**
	 * 添加表字段
	 * @param moduleid
	 * @param tablename
	 * @param target
	 * @param column_comment
	 * @param column_type
	 * @param length
	 * @param decimal
	 * @param column_default
	 * @param is_nullable
	 * @param foreignTable
	 * @param foreignKey
	 * @return
	 * @throws Exception
	 */
	public InvokeResult addColumns(long moduleid, String tablename, String target, String column_comment, String column_type, Integer length, Integer decimal, String column_default, String is_nullable, Long foreignTable, String foreignKey) throws Exception {
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				try {
					Columns column = new Columns();
					String columnstr = "";
					String type = column_type;
					if (!"text".equalsIgnoreCase(type) && !"longtext".equalsIgnoreCase(type) && !"date".equalsIgnoreCase(type) && !"datetime".equalsIgnoreCase(type)) {
						if ("select".equalsIgnoreCase(type)) {
							column.setForeignTable(foreignTable);
							column.setForeignKey(foreignKey);
							type = "bigint";
						} else if ("sex".equalsIgnoreCase(type) || "yesorno".equalsIgnoreCase(type) || "haveorno".equalsIgnoreCase(type)) {
							type = "int";
						}else if ("file".equalsIgnoreCase(type) ) {
                            type = "varchar";
                        }
						if (length != null && length > 0) {
							type += "("+length;
							if (decimal != null && decimal > 0) {
								type += ", "+decimal;
							}
							type += ")";
						}
					}
					String notnull = "NULL";
					if ("NO".equalsIgnoreCase(is_nullable)) {
						notnull = "NOT NULL";
					}
					String defaultstr = "";
					if (CommonUtils.isNotEmpty(column_default)) {
						defaultstr = " DEFAULT "+column_default;
					}
					String after = "";
					Record afterColumn;
					int ordinal_position = 0;
					if (CommonUtils.isNotEmpty(target)) {
						Record targetColumn = getColumn(tablename, "column_name", target);
						Integer position = targetColumn.getInt("ordinal_position");
						ordinal_position = position;
						position -= 1;
						afterColumn = getColumn(tablename, "ordinal_position", position);
						after = afterColumn.getStr("column_name");
						if (!cdao.updatePosition(moduleid, ordinal_position)) {
							return false;
						}
					} else {
						afterColumn = cdao.getLastColumn(tablename);
						after = afterColumn.getStr("column_name");
						ordinal_position = afterColumn.getInt("ordinal_position") + 1;
					}
					String fieldnum = getNextColumnNum(moduleid);
					column.setColumnComment(column_comment).setColumnName("field"+fieldnum).setColumnType(column_type).setLengths(length)
							.setDecimals(decimal).setColumnDefault(column_default).setIsNullable(is_nullable).setModuleid(moduleid)
							.setOrdinalPosition(ordinal_position);
					if (!column.save()) {
						return false;
					}
					columnstr = " `field"+fieldnum+"`  "+type+" "+notnull+" "+defaultstr+" COMMENT '"+column_comment+"' AFTER "+after+";";
					if (!cdao.addColumns(tablename, columnstr, moduleid, fieldnum)) {
						return false;
					}
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
				return true;
			}
		});
		if (flag) {
			return InvokeResult.success();
		} else {
			return InvokeResult.failure("添加字段失败！");
		}
	}

	/**
	 * 修改表字段
	 * @param moduleid
	 * @param tablename
	 * @param column_name
	 * @param column_comment
	 * @param column_type
	 * @param length
	 * @param decimal
	 * @param column_default
	 * @param is_nullable
	 * @param foreignTable
	 * @param foreignKey
	 * @return
	 * @throws Exception
	 */
	public InvokeResult editColumns(long moduleid, String tablename, String column_name, String column_comment, String column_type, Integer length, Integer decimal, String column_default, String is_nullable, Long foreignTable, String foreignKey) throws Exception {
        Record targetColumn = getColumn(tablename, "column_name", column_name);
        Integer position = targetColumn.getInt("ordinal_position");
        Columns column = cdao.findByPosition(moduleid, position);
	    List<Record> records = Db.find("select * from "+tablename);
        if(records.size()>0){
            if(!column.getColumnType().equalsIgnoreCase(column_type)){
                return InvokeResult.failure("表单数据已存在，禁止修改字段类型！");
            }
        }
	    boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				try {
					Record targetColumn = getColumn(tablename, "column_name", column_name);
                    Integer position = targetColumn.getInt("ordinal_position");
                    Columns column = cdao.findByPosition(moduleid, position);
					String columnstr = "";
					String type = column_type;
                    column.setForeignTable(null);
                    column.setForeignKey(null);
					if (!"text".equalsIgnoreCase(type) && !"longtext".equalsIgnoreCase(type) && !"date".equalsIgnoreCase(type) && !"datetime".equalsIgnoreCase(type)) {
						if ("select".equalsIgnoreCase(type)) {
							column.setForeignTable(foreignTable);
							column.setForeignKey(foreignKey);
							type = "bigint";
						} else if ("sex".equalsIgnoreCase(type) || "yesorno".equalsIgnoreCase(type) || "haveorno".equalsIgnoreCase(type)) {
							type = "int";
						}
						if (length != null && length > 0) {
							type += "("+length;
							if (decimal != null && decimal > 0) {
								type += ", "+decimal;
							}
							type += ")";
						}
					}
					String notnull = "NULL";
					if ("NO".equalsIgnoreCase(is_nullable)) {
						notnull = "NOT NULL";
					}
					String defaultstr = "";
					if (CommonUtils.isNotEmpty(column_default)) {
						defaultstr = " DEFAULT "+column_default;
					}
					String after = "";
					Record afterColumn;
					column.setColumnComment(column_comment).setColumnType(column_type).setLengths(length)
							.setDecimals(decimal).setColumnDefault(column_default).setIsNullable(is_nullable);
					if (!column.update()) {
						return false;
					}
					position -= 1;
					afterColumn = getColumn(tablename, "ordinal_position", position);
					after = afterColumn.getStr("column_name");
					columnstr = " `"+column_name+"`  "+type+" "+notnull+" "+defaultstr+" COMMENT '"+column_comment+"' AFTER "+after+";";
					if (!cdao.editColumns(tablename, columnstr)) {
						return false;
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
				return true;
			}
		});
		if (flag) {
			return InvokeResult.success();
		} else {
			return InvokeResult.failure("添加字段失败！");
		}
	}

    /**
     * 表字段删除
     * @param ids
     * @return
     * @throws Exception
     */
    public InvokeResult deleteColumns(String ids,String tablename) throws Exception {
        log.info("表字段删除");
        boolean flag = Db.tx(new IAtom() {
            @Override
            public boolean run() throws SQLException {
                try {
                    String idarr[] = ids.split(",");
                    for (String id : idarr) {
                        Columns column = cdao.findById(id);
                        if (!cdao.deleteColumns(tablename, column.getColumnName())) {
                            return false;
                        }else{
                            if (!column.delete()){
                                return false;
                            }
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    return false;
                }
                return true;
            }
        });
        if (flag) {
            return InvokeResult.success();
        } else {
            return InvokeResult.failure("删除字段失败！");
        }
    }
	/**
	 * 字段位置移动
	 * @param columnid
	 * @param tablename
	 * @param column
	 * @param updown
	 * @return
	 * @throws Exception
	 */
	public InvokeResult move(long columnid, String tablename, String column, int updown) throws Exception {
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				try {
					Record currColumn = getColumn(tablename, "column_name", column);
					String defaultstr = "";
					if (CommonUtils.isNotEmpty(currColumn.getStr("column_default"))) {
						defaultstr = " DEFAULT "+currColumn.getStr("column_default");
					}
					String ifnull = "YES".equalsIgnoreCase(currColumn.getStr("is_nullable"))?"NULL":"NOT NULL";
					String columnstr = " "+currColumn.getStr("column_name")+
							" "+currColumn.getStr("column_type")+
							" "+ifnull+
							" "+defaultstr+
							" COMMENT '"+currColumn.getStr("column_comment")+"'";
					Integer position = currColumn.getInt("ordinal_position");
					Integer colpos = position;
					Integer tablecolpos = position;
					if (updown == 1) {
						tablecolpos -= 2;
						colpos -= 1;
					} else {
						tablecolpos += 1;
						colpos += 1;
					}
					Record targetColumn = getColumn(tablename, "ordinal_position", tablecolpos);
					String target = targetColumn.getStr("column_name");
					if (!cdao.move(tablename, columnstr, target)) {
						return false;
					}
					if (!cdao.move(columnid, position, colpos)) {
						return false;
					}
				} catch (Exception e) {
					e.printStackTrace();
					return false;
				}
				return true;
			}
		});
		if (flag) {
			return InvokeResult.success();
		} else {
			return InvokeResult.failure("字段移动失败！");
		}
	}
	
	public boolean alterTable() throws Exception {
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Db.update("DROP TABLE IF EXISTS cmkj0001;");
				Db.update("CREATE TABLE cmkj0001 (" +
						"    id INT(11) NOT NULL AUTO_INCREMENT," +
						"    name VARCHAR(25)," +
						"    deptId INT(11)," +
						"    salary FLOAT," +
						"    PRIMARY KEY (`id`)" +
						"    );");
				return true;
			}
		});
		return flag;
	}
	
	public boolean dropTable() throws Exception {
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Db.update("DROP TABLE IF EXISTS cmkj0001;");
				String dropstr = "DROP TABLE IF EXISTS cmkj0001_attach;";
				Db.update("CREATE TABLE cmkj0001 (" +
						"    id INT(11) NOT NULL AUTO_INCREMENT," +
						"    name VARCHAR(25)," +
						"    deptId INT(11)," +
						"    salary FLOAT," +
						"    PRIMARY KEY (`id`)" +
						"    );");
				return true;
			}
		});
		return flag;
	}

	/**
	 * 获取关联表单列表
	 * @param moduleid
	 * @return
	 * @throws Exception
	 */
	public List<Modules> getForeignTables(Long moduleid) throws Exception {
		return dao.getForeignTables(moduleid);
	}
	
	public String getNextTableNum() throws Exception {
		Modules module = dao.getLastModule();
		int num = 0;
		if (module == null) {
			num = 1;
		} else {
			num = CommonUtils.converInteger(module.getStr("tablename")) + 1;
		}
		return getNum(num);
	}

	public String getNextColumnNum(long moduleid) throws Exception {
		Modules module = dao.findById(moduleid);
		int num = CommonUtils.converInteger(module.getStr("lastCnum")) + 1;
		return getNum(num);
	}
	
	private String getNum(int num) {
		if (num < 10) {
			return "000"+num;
		} else if (num >= 10 && num < 100) {
			return "00"+num;
		} else if (num >= 100 && num < 1000) {
			return "0"+num;
		} else {
			return ""+num;
		}
	}

    public InvokeResult saveCompetence(Long userid,Long moduleid,Integer type) throws Exception {
        log.info("保存用户权限");
        ModuleUser moduleUser = new ModuleUser();
        moduleUser.setUser(userid);
        moduleUser.setModule(moduleid);
        moduleUser.setType(type);
        return ModuleUser.dao.save(moduleUser);
    }
    public InvokeResult updateCompetence(Long competenceid,Long userid,Long moduleid,Integer type) throws Exception {
        log.info("保存用户权限");
        ModuleUser moduleUser = ModuleUser.dao.findById(competenceid);
        if(moduleUser==null){
            return InvokeResult.failure("修改失败");
        }
        moduleUser.setUser(userid);
        moduleUser.setModule(moduleid);
        moduleUser.setType(type);
        boolean flg = moduleUser.update();
        if(flg){
            return InvokeResult.success();
        }else{
            return InvokeResult.failure("修改失败");
        }
    }

    /*public InvokeResult saveModuleUser(Integer resid, String userIds) {
	    boolean flag = Db.tx(new IAtom() {
            @Override
            public boolean run() throws SQLException {
                Db.update("delete from res_user where res_id = ?", resid);
                if(CommonUtils.isNotEmpty(userIds)){
                    List<String> sqlList=Lists.newArrayList();
                    for(String id : userIds.split(",")){
                        if(CommonUtils.isNotEmpty(id)){
                            if(!Integer.valueOf(id).equals(10000)){
                                sqlList.add("insert into res_user (res_id,user_id) values ("+resid+","+Integer.valueOf(id)+")");
                            }
                        }
                    }
                    Db.batch(sqlList, 50);
                    CacheClearUtils.clearUserMenuCache();
                }
				CacheKit.removeAll(CacheName.userMenuCache);
                return true;
            }
        });
        if (flag) {
            return InvokeResult.success();
        } else {
            return InvokeResult.failure("分配人员权限失败！");
        }
    }*/

	public InvokeResult saveModuleUser(String resid, String userIds) {
		boolean flag = Db.tx(new IAtom() {
			@Override
			public boolean run() throws SQLException {
				Db.update("delete from res_user where res_id in ("+resid+")");
				if(CommonUtils.isNotEmpty(userIds)){
					List<String> sqlList=Lists.newArrayList();
					for(String id : userIds.split(",")){
						if(CommonUtils.isNotEmpty(id)){
							if(!Integer.valueOf(id).equals(10000)){
								String[] resids = resid.split(",");
								for (String rid : resids) {
									sqlList.add("insert into res_user (res_id,user_id) values ("+rid+","+Integer.valueOf(id)+")");
								}
							}
						}
					}
					Db.batch(sqlList, 50);
					CacheClearUtils.clearUserMenuCache();
				}
				CacheKit.removeAll(CacheName.userMenuCache);
				return true;
			}
		});
		if (flag) {
			return InvokeResult.success();
		} else {
			return InvokeResult.failure("分配人员权限失败！");
		}
	}

	public InvokeResult saveOrderby(Long columnsid,Long moduleid,Integer type) throws Exception {
		log.info("保存排序查询");
        Order order = new Order();
        order.setModuleid(moduleid);
        order.setColumnsid(columnsid);
        Columns columns = Columns.dao.findById(columnsid);
        Modules modules = Modules.dao.findById(moduleid);
        order.setTablename(modules.getUrl());
        order.setColumnName(columns.getColumnName());
        order.setColumnComment(columns.getColumnComment());
        order.setOrder(type);
		return Order.dao.save(order);
	}

    public InvokeResult updateOrderby(Long orderid,Long columnsid,Long moduleid,Integer type) throws Exception {
        log.info("修改排序");
        Order order = Order.dao.findById(orderid);
        if(order==null){
            return InvokeResult.failure("修改失败");
        }
        order.setModuleid(moduleid);
        order.setColumnsid(columnsid);
        Columns columns = Columns.dao.findById(columnsid);
        Modules modules = Modules.dao.findById(moduleid);
        order.setTablename(modules.getUrl());
        order.setColumnName(columns.getColumnName());
        order.setColumnComment(columns.getColumnComment());
        order.setOrder(type);
        boolean flg = order.update();
        if(flg){
            return InvokeResult.success();
        }else{
            return InvokeResult.failure("修改失败");
        }
    }

	public InvokeResult setIsInterface(String id, Integer status) throws Exception {
		log.info("模块状态变更");
		return dao.setIsInterface(id, status);
	}
}
